import pymysql

connection = pymysql.connect(
    host='localhost',
    user='root',
    password='ADthq123',
    database='StudentCourseSystem',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

# 用户验证
def verify_user(student_id, password):
    with connection.cursor() as cursor:
        sql = "SELECT * FROM students WHERE student_id=%s AND password=%s"
        cursor.execute(sql, (student_id, password))
        result = cursor.fetchone()
        return result is not None
    
# 获取课程信息
# def get_courses():
    with connection.cursor() as cursor:
        sql = "SELECT * FROM courses"
        cursor.execute(sql)
        return cursor.fetchall()
    
# 选课
# def enroll_course(student_id, course_id):
    with connection.cursor() as cursor:
        # 检查是否已选该课程
        sql = "SELECT * FROM enrollments WHERE student_id=%s AND course_id=%s"
        cursor.execute(sql, (student_id, course_id))
        if cursor.fetchone():
            return "您已选过该课程！"

        # 检查课程时间是否冲突
        sql = """
            SELECT c.schedule FROM courses c
            JOIN enrollments e ON c.course_id = e.course_id
            WHERE e.student_id=%s
        """
        cursor.execute(sql, (student_id,))
        existing_schedules = [row['schedule'] for row in cursor.fetchall()]

        # 获取新课程的时间
        sql = "SELECT schedule FROM courses WHERE course_id=%s"
        cursor.execute(sql, (course_id,))
        new_schedule = cursor.fetchone()['schedule']

        if new_schedule in existing_schedules:
            return "课程时间冲突，无法选课！"

        # 插入新记录
        sql = "INSERT INTO enrollments (student_id, course_id) VALUES (%s, %s)"
        cursor.execute(sql, (student_id, course_id))
        connection.commit()
        return "选课成功！"
    
# 退课
# def drop_course(student_id, course_id):
    with connection.cursor() as cursor:
        # 检查是否已选该课程
        sql = "SELECT * FROM enrollments WHERE student_id=%s AND course_id=%s"
        cursor.execute(sql, (student_id, course_id))
        if not cursor.fetchone():
            return "您未选该课程！"
        # 删除记录
        sql = "DELETE FROM enrollments WHERE student_id=%s AND course_id=%s"
        cursor.execute(sql, (student_id, course_id))
        connection.commit()
        return "退课成功！"
    
# def get_enrolled_courses(student_id):
    with connection.cursor() as cursor:
        sql = """
            SELECT c.* FROM courses c
            JOIN enrollments e ON c.course_id = e.course_id
            WHERE e.student_id=%s
        """
        cursor.execute(sql, (student_id,))
        return cursor.fetchall()


# 获取课程列表
def get_courses():
    with connection.cursor() as cursor:
        sql = "SELECT * FROM courses"
        cursor.execute(sql)
        return cursor.fetchall()

# 选课
def enroll_course(student_id, course_id):
    with connection.cursor() as cursor:
        # 检查是否已选该课程
        sql = "SELECT * FROM enrollments WHERE student_id=%s AND course_id=%s"
        cursor.execute(sql, (student_id, course_id))
        if cursor.fetchone():
            return "您已选过该课程！"

        # 检查课程时间是否冲突（可选）
        sql = """
            SELECT c.schedule FROM courses c
            JOIN enrollments e ON c.course_id = e.course_id
            WHERE e.student_id=%s
        """
        cursor.execute(sql, (student_id,))
        existing_schedules = [row['schedule'] for row in cursor.fetchall()]

        # 获取新课程的时间
        sql = "SELECT schedule FROM courses WHERE course_id=%s"
        cursor.execute(sql, (course_id,))
        new_schedule = cursor.fetchone()['schedule']

        if new_schedule in existing_schedules:
            return "课程时间冲突，无法选课！"

        # 插入新记录
        sql = "INSERT INTO enrollments (student_id, course_id) VALUES (%s, %s)"
        cursor.execute(sql, (student_id, course_id))
        connection.commit()
        return "选课成功！"

# 退课
def drop_course(student_id, course_id):
    with connection.cursor() as cursor:
        # 检查是否已选该课程
        sql = "SELECT * FROM enrollments WHERE student_id=%s AND course_id=%s"
        cursor.execute(sql, (student_id, course_id))
        if not cursor.fetchone():
            return "您未选该课程！"
        # 删除记录
        sql = "DELETE FROM enrollments WHERE student_id=%s AND course_id=%s"
        cursor.execute(sql, (student_id, course_id))
        connection.commit()
        return "退课成功！"

# 获取已选课程
def get_enrolled_courses(student_id):
    with connection.cursor() as cursor:
        sql = """
            SELECT c.* FROM courses c
            JOIN enrollments e ON c.course_id = e.course_id
            WHERE e.student_id=%s
        """
        cursor.execute(sql, (student_id,))
        return cursor.fetchall()